package com.ningxia.water.mapper;

import com.ningxia.water.entity.TsMonthData;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ningxia.water.vo.out.MonthConsumedVo;
import com.ningxia.water.vo.out.MonthReportVo;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author hz
 * @since 2021-03-15
 */
public interface TsMonthDataMapper extends BaseMapper<TsMonthData> {


    /**
     * 每月用水量总额
     * @param time
     * @return
     */
    @Select("select sum(month_data)/10000 as totalVolume from ts_month_data where well_id = #{wellId} and time = #{time}")
    Double getMonthVolumeByWellId(@Param("wellId") Integer wellId,@Param("time") String time);

    /**
     * 该机井一年内的每个月用水量
     * @param wellId
     * @param startTime
     * @param endTime
     * @return
     */
    @Select("SELECT\n" +
            "\tmd.time as month,\n" +
            "\tsum( md.month_data )/ 10000 as volume\n" +
            "FROM\n" +
            "\tts_month_data md \n" +
            "WHERE\n" +
            "  md.well_id = #{wellId} and \n" +
            "\tmd.time BETWEEN #{startTime} AND #{endTime} \n" +
            "GROUP BY\n" +
            "\tmd.time")
    List<MonthConsumedVo> getMonthConsumedByWellId(Integer wellId, String startTime, String endTime);

    /**
     * 获取指定乡镇或工业园区的用户的许可用水量和计划用水量
     * @param year
     * @param zoneType
     * @param zoneId
     * @return
     */
    @Select("SELECT\n" +
            "\tap.applicant_id,\n" +
            "\tli.license_volume / 10000 AS licenseVolume,\n" +
            "\tsum( ap.plan_volume )/ 10000 AS planVolume\n" +
            "FROM\n" +
            "\tts_applicant_info ai\n" +
            "\tLEFT JOIN ts_license_info li ON ai.applicant_id = li.applicant_id\n" +
            "\tLEFT JOIN ts_applicant_plan ap ON ai.applicant_id = ap.applicant_id \n" +
            "WHERE\n" +
            "\tap.plan_year = #{year} \n" +
            "\tAND li.available = 1 AND ai.zone_type = #{zoneType} AND ai.zone_id = #{zoneId}\n" +
            "GROUP BY\n" +
            "\tai.applicant_id")
    List<MonthReportVo> getApplicantsInfos(String year, Integer zoneType, Integer zoneId);

    /**
     * 获取指定乡镇或工业园区下用户的累计用水量、工业用水量、农业用水量、生产用水量、生活用水量
     * @param month
     * @param zoneType
     * @param zoneId
     * @return
     */
    @Select("SELECT\n" +
            "\tai.applicant_id,\n" +
            "\tai.applicant_name,\n" +
            "\tsum( md.month_data )/ 10000 totalVolume,\n" +
            "\tsum( CASE WHEN wi.well_usage = 1 THEN md.month_data ELSE 0 END )/ 10000 AS industryVolume,\n" +
            "\tsum( CASE WHEN wi.well_usage = 2 THEN md.month_data ELSE 0 END )/ 10000 AS agricultureVolume,\n" +
            "\tsum( CASE WHEN wi.well_usage = 3 THEN md.month_data ELSE 0 END )/ 10000 AS lifeVolume,\n" +
            "\tsum( CASE WHEN wi.well_usage = 4 THEN md.month_data ELSE 0 END )/ 10000 AS productVolume\n" +
            "FROM\n" +
            "\tts_applicant_info ai\n" +
            "\tLEFT JOIN ts_applicant_well aw ON ai.applicant_id = aw.applicant_id\n" +
            "\tLEFT JOIN ts_month_data md ON aw.well_id = md.well_id\n" +
            "\tLEFT JOIN ts_well_info wi ON md.well_id = wi.well_id\n" +
            "WHERE\n" +
            "\tmd.time = #{month} AND ai.zone_type = #{zoneType} AND ai.zone_id = #{zoneId}\n" +
            "GROUP BY\n" +
            "\tai.applicant_id \n" +
            "ORDER BY\n" +
            "\tai.applicant_id")
    List<MonthReportVo> getVolumeInfos(String month, Integer zoneType, Integer zoneId);
}
